package com.example.androidsx;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

public class SQLcj extends SQLiteOpenHelper {

    public static final String DB_NAME = "Users.db";
    public static final int DB_VERSION = 6;
    // 表名常量
    public static final String TABLE_NORMAL_USER = "ptuser";
    public static final String TABLE_ADMIN_USER = "gluser";
    public static final String TABLE_PRODUCT = "Commod";
    public static final String TABLE_SHOP="Shopping";

    // 字段名
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_USER_ID = "_id";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_PWD = "pwd";
    public static final String COLUMN_EML = "eml";
    public static final String COLUMN_MONEY = "money";
    public static final String COLUMN_NUMBER = "number";
    public static final String COLUMN_TYPE = "type";
    //购物车字段名
    public static final String COLUMN_SHOP_NUMBER="shopnumber";
    public static final String COLUMN_SHOP_MONER="shopmoney";
    public static final String COLUMN_SHOP_TYPE="shoptype";

    public SQLcj(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE " + TABLE_NORMAL_USER + " (" +COLUMN_USER_ID +
                " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_NAME + " VARCHAR(20) UNIQUE, " +
                COLUMN_PWD + " VARCHAR(20), " +
                COLUMN_EML + " VARCHAR(20))");


        db.execSQL("CREATE TABLE " + TABLE_ADMIN_USER + " (" +
                COLUMN_ID + " VARCHAR(20) PRIMARY KEY, " +
                COLUMN_NAME + " VARCHAR(20), " +
                COLUMN_PWD + " VARCHAR(20), " +
                COLUMN_EML + " VARCHAR(20))");


        db.execSQL("CREATE TABLE " + TABLE_PRODUCT + " (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_MONEY + " DECIMAL, " +
                COLUMN_NUMBER + " INT, " +
                COLUMN_TYPE + " VARCHAR(10))");


        db.execSQL("CREATE TABLE " + TABLE_SHOP + " ( " +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_SHOP_MONER + " DECIMAL, "
                + COLUMN_SHOP_NUMBER + " INT, "
                + COLUMN_SHOP_TYPE + " VARCHAR(10))");



        initSampleData(db);


    }

    private void initSampleData(SQLiteDatabase db) {

        ContentValues user = new ContentValues();
        user.put(COLUMN_NAME, "张三");
        user.put(COLUMN_PWD, "123456");
        user.put(COLUMN_EML, "zhangsan@example.com");
        db.insert(TABLE_NORMAL_USER, null, user);


        ContentValues admin = new ContentValues();
        admin.put(COLUMN_ID, "admin001");
        admin.put(COLUMN_NAME, "管理员");
        admin.put(COLUMN_PWD, "admin123");
        admin.put(COLUMN_EML, "admin@example.com");
        db.insert(TABLE_ADMIN_USER, null, admin);


        ContentValues product = new ContentValues();
        product.put(COLUMN_MONEY, 29.9);
        product.put(COLUMN_NUMBER, 100);
        product.put(COLUMN_TYPE, "图书");
        db.insert(TABLE_PRODUCT, null, product);


        ContentValues shop=new ContentValues();
        shop.put(COLUMN_SHOP_MONER,29.9);
        shop.put(COLUMN_SHOP_NUMBER,2);
        shop.put(COLUMN_SHOP_TYPE,"图书");
        db.insert(TABLE_SHOP,null,shop);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NORMAL_USER);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ADMIN_USER);
       db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCT);




            if (oldVersion < 6) {

                db.execSQL("CREATE TABLE " + TABLE_SHOP + " (" +
                        "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        COLUMN_SHOP_MONER + " DECIMAL, " +
                        COLUMN_SHOP_NUMBER + " INTEGER, " +
                        COLUMN_SHOP_TYPE + " TEXT)");
            }


        //onCreate(db);
    }

    //判断是否账号重复添加
    public boolean isUsernameExists(String id,String username) {
        if (id==null){
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.query(
                    TABLE_NORMAL_USER,
                    new String[]{COLUMN_NAME},
                    COLUMN_NAME + " = ?",
                    new String[]{username},
                    null, null, null);
            boolean exists = cursor.getCount() > 0;
            cursor.close();
            return exists;
        }else{
            SQLiteDatabase db = this.getReadableDatabase();
            Cursor cursor = db.query(
                    TABLE_ADMIN_USER,
                    new String[]{COLUMN_NAME},
                    COLUMN_NAME + " = ?",
                    new String[]{username},
                    null, null, null);
            boolean exists = cursor.getCount() > 0;
            cursor.close();
            return exists;
        }

    }
    //添加商品
    public long addPradut(double muney,int number,String type){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues values = new ContentValues();
        try{
            values.put(COLUMN_MONEY,muney);
            values.put(COLUMN_NUMBER,muney);
            values.put(COLUMN_TYPE,type);
            return db.insert(TABLE_PRODUCT,null,values);

        } catch (Exception e) {
            Log.e("SQLcj","商品添加错误"+e.getMessage());
            return -1;
        }
    }

    //添加账号
    public long addAccount(String id, String username, String password, String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put(COLUMN_NAME, username);
            values.put(COLUMN_PWD, password);
            values.put(COLUMN_EML, email);

            if (id != null && !id.isEmpty()) {
                values.put(COLUMN_ID, id);
                return db.insert(TABLE_ADMIN_USER, null, values);
            } else {
                long result = db.insert(TABLE_NORMAL_USER, null, values);
                if (result == -1) {
                    Log.e("SQLcj", "插入普通用户失败，请检查表是否存在");
                }
                return result;
            }
        } catch (Exception e) {
            Log.e("SQLcj", "添加用户错误: " + e.getMessage());
            return -1;
        } finally {
            db.close();
        }
    }

    //商品相关方法 ,显示商品列表
    public long addProduct(double price, int quantity, String type) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_MONEY, price);
        values.put(COLUMN_NUMBER, quantity);
        values.put(COLUMN_TYPE, type);
        return db.insert(TABLE_PRODUCT, null, values);
    }
//获取所有商品信息
    public Cursor getAllProducts() {
        SQLiteDatabase db = this.getReadableDatabase();
       return db.query(TABLE_PRODUCT,
               new String[]{"_id",COLUMN_MONEY,COLUMN_NUMBER,COLUMN_TYPE},
               null,null,null,null,
               COLUMN_TYPE + " ASC");

    }

    //获取所有账号
    public Cursor getAllAccounts() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(
                TABLE_NORMAL_USER,
                new String[]{ COLUMN_USER_ID + " AS _id",COLUMN_NAME, COLUMN_PWD, COLUMN_EML},
                null, null, null, null,
                COLUMN_NAME + " ASC"
        );
    }
//查询账户信息
    public boolean validateLogin(String id, String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        if(id.isEmpty()||id==null){
             Cursor cursor = db.query(
             TABLE_NORMAL_USER,
            new String[]{COLUMN_USER_ID},
            COLUMN_NAME + " = ? AND " + COLUMN_PWD + " = ?",
            new String[]{username, password},
            null, null, null
    );
    boolean isValid = cursor.getCount() > 0;
    cursor.close();
    return isValid;
}else {
    // 查管理员表
       Cursor cursor = db.query(
                TABLE_ADMIN_USER,
                new String[]{COLUMN_ID},
               COLUMN_ID+" = ? AND "+
                COLUMN_NAME + " = ? AND " + COLUMN_PWD + " = ?",
                new String[]{id,username, password},
                null, null, null
        );
      boolean  isValid = cursor.getCount() > 0;
        cursor.close();

    return isValid;
    }

}
}

